package com.hcc.flow.server.common.utils;

import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellReference;
import org.springframework.util.CollectionUtils;

import com.hcc.flow.server.common.annotation.FieldTypeAnnotation;

/**
 * excel工具类
 * 
 * @author 韩长长 
 *
 */
public class ExcelUtil {

	public static void excelLocal(String path, String fileName, String[] headers, List<Object[]> datas) {
		Workbook workbook = getWorkbook(headers, datas);
		if (workbook != null) {
			ByteArrayOutputStream byteArrayOutputStream = null;
			FileOutputStream fileOutputStream = null;
			try {
				byteArrayOutputStream = new ByteArrayOutputStream();
				workbook.write(byteArrayOutputStream);

				String suffix = ".xls";
				File file = new File(path + File.separator + fileName + suffix);
				if (!file.getParentFile().exists()) {
					file.getParentFile().mkdirs();
				}

				fileOutputStream = new FileOutputStream(file);
				fileOutputStream.write(byteArrayOutputStream.toByteArray());
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				try {
					if (fileOutputStream != null) {
						fileOutputStream.close();
					}
				} catch (IOException e) {
					e.printStackTrace();
				}
				try {
					if (byteArrayOutputStream != null) {
						byteArrayOutputStream.close();
					}
				} catch (IOException e) {
					e.printStackTrace();
				}

				try {
					workbook.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
	}

	/**
	 * 导出excel
	 * 
	 * @param fileName
	 * @param headers
	 * @param datas
	 * @param response
	 */
	public static void excelExport(String fileName, String[] headers, List<Object[]> datas,
			HttpServletResponse response) {
		Workbook workbook = getWorkbook(headers, datas);
		if (workbook != null) {
			ByteArrayOutputStream byteArrayOutputStream = null;
			try {
				byteArrayOutputStream = new ByteArrayOutputStream();
				workbook.write(byteArrayOutputStream);

				String suffix = ".xls";
				response.setContentType("application/vnd.ms-excel;charset=utf-8");
				response.setHeader("Content-Disposition",
						"attachment;filename=" + new String((fileName + suffix).getBytes(), "iso-8859-1"));

				OutputStream outputStream = response.getOutputStream();
				outputStream.write(byteArrayOutputStream.toByteArray());
				outputStream.close();
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				try {
					if (byteArrayOutputStream != null) {
						byteArrayOutputStream.close();
					}
				} catch (IOException e) {
					e.printStackTrace();
				}

				try {
					workbook.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
	}

	/**
	 * 
	 * @param headers
	 *            列头
	 * @param datas
	 *            数据
	 * @return
	 */
	public static Workbook getWorkbook(String[] headers, List<Object[]> datas) {
		Workbook workbook = new HSSFWorkbook();

		Sheet sheet = workbook.createSheet();
		Row row = null;
		Cell cell = null;
		CellStyle style = workbook.createCellStyle();
		style.setAlignment(HorizontalAlignment.CENTER_SELECTION);

		Font font = workbook.createFont();

		int line = 0, maxColumn = 0;
		if (headers != null && headers.length > 0) {// 设置列头
			row = sheet.createRow(line++);
			row.setHeightInPoints(23);
			font.setBold(true);
			font.setFontHeightInPoints((short) 13);
			style.setFont(font);

			maxColumn = headers.length;
			for (int i = 0; i < maxColumn; i++) {
				cell = row.createCell(i);
				cell.setCellValue(headers[i]);
				cell.setCellStyle(style);
			}
		}

		if (datas != null && datas.size() > 0) {// 渲染数据
			for (int index = 0, size = datas.size(); index < size; index++) {
				Object[] data = datas.get(index);
				if (data != null && data.length > 0) {
					row = sheet.createRow(line++);
					row.setHeightInPoints(20);

					int length = data.length;
					if (length > maxColumn) {
						maxColumn = length;
					}

					for (int i = 0; i < length; i++) {
						cell = row.createCell(i);
						cell.setCellValue(data[i] == null ? null : data[i].toString());
					}
				}
			}
		}

		for (int i = 0; i < maxColumn; i++) {
			sheet.autoSizeColumn(i);
		}

		return workbook;
	}

	/**
	 * @Title: exportExcel
	 * @Description: 导出Excel的方法
	 * @author: evan @ 2014-01-09
	 * @param workbook
	 * @param sheetNum
	 *            (sheet的位置，0表示第一个表格中的第一个sheet)
	 * @param sheetTitle
	 *            （sheet的名称）
	 * @param headers
	 *            （表格的标题）
	 * @param result
	 *            （表格的数据）
	 * @param out
	 *            （输出流）
	 * @throws Exception
	 */
	public static void exportExcel(HSSFWorkbook workbook, int sheetNum, String sheetTitle, String[] headers,
			List<Object[]> result, OutputStream out) {
		// 生成一个表格
		HSSFSheet sheet = workbook.createSheet();
		workbook.setSheetName(sheetNum, sheetTitle);
		// 设置表格默认列宽度为20个字节
		sheet.setDefaultColumnWidth((short) 20);
		
		// 生成一个样式
		HSSFCellStyle style = workbook.createCellStyle();
		// 设置这些样式
		style.setFillForegroundColor(HSSFColor.HSSFColorPredefined.PALE_BLUE.getIndex());
		style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		style.setBorderBottom(BorderStyle.THIN);
		style.setBorderLeft(BorderStyle.THIN);
		style.setBorderRight(BorderStyle.THIN);
		style.setBorderTop(BorderStyle.THIN);
		style.setAlignment(HorizontalAlignment.CENTER);
		// 生成一个字体
		HSSFFont font = workbook.createFont();
		font.setColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
		font.setFontHeightInPoints((short) 12);
		font.setBold(true);
		// 把字体应用到当前的样式
		style.setFont(font);

		// 指定当单元格内容显示不下时自动换行
		style.setWrapText(true);

		// 产生表格标题行
		HSSFRow row = sheet.createRow(0);
		List<Integer> heIndexs = new ArrayList<>(); 
		for (int i = 0; i < headers.length; i++) {
			HSSFCell cell = row.createCell((short) i);
			cell.setCellStyle(style);
			HSSFRichTextString text = new HSSFRichTextString(headers[i]);
			cell.setCellValue(text.toString());
			if(headers[i].contains("元") || headers[i].contains("数") || headers[i].contains("个") || headers[i].contains("秒") || headers[i].contains("天") || headers[i].contains("体量")){
				heIndexs.add(i);
			}
			if(headers[i].equals("标识ID")){
				sheet.setColumnWidth(i, (short) 10*256);
			}else if(headers[i].contains("ID")){
				sheet.setColumnWidth(i, (short) 35*256);
			}else if(headers[i].contains("完整地址")){
				sheet.setColumnWidth(i, (short) 70*256);
			}
		}
		
		// 遍历集合数据，产生数据行
		if (result != null) {
			int index = 1;
			int cellIndex = 0;
			for (Object[] m : result) {
				row = sheet.createRow(index);
				cellIndex = 0;
				for (Object str : m) {
					HSSFCell cell = row.createCell((short) cellIndex);
					if(str instanceof String){
						cell.setCellValue(str != null ? str.toString() : "");
					}else if(str instanceof Integer || str instanceof Long){
						cell.setCellValue(CommUtil.null2Long(str));
					}else if(str instanceof BigDecimal || str instanceof Double){
						cell.setCellValue(CommUtil.null2Double(str));
					}
					cellIndex++;
				}
				index++;
			}
			// 生成一个样式
			HSSFCellStyle styleLast = workbook.createCellStyle();
			// 设置这些样式
			styleLast.setFillForegroundColor(HSSFColor.HSSFColorPredefined.PALE_BLUE.getIndex());
			HSSFFont fontLast = workbook.createFont();
			fontLast.setColor(HSSFColor.HSSFColorPredefined.RED.getIndex());
			fontLast.setFontHeightInPoints((short) 12);
			fontLast.setBold(true);
			// 把字体应用到当前的样式
			styleLast.setFont(fontLast);
			//styleLast.setFont(font);
			row = sheet.createRow(index+1);
			HSSFCell cell = row.createCell(0);
			cell.setCellStyle(styleLast);
			cell.setCellValue("合计：");
			for (int i = 2; i < cellIndex; i++) {
				cell = row.createCell(i);//设置公式前，一定要先建立表格
				cell.setCellStyle(styleLast);
				if(heIndexs.contains(i)){
					String colString = CellReference.convertNumToColString(i); //长度转成ABC列
					String sumstring = "SUM(" + colString + "2:" + colString + index + ")";//求和公式
					row.getCell(i).setCellFormula(sumstring);
				}
			}
		}
		sheet.createFreezePane(0, 1);
	}

	public static void execlOneSheet1(HSSFWorkbook workbook, OutputStream outputStream, List<Map<Object, Object>> list,
			List<String> params, Class<?> pp, String sheetName, int index, Boolean timespan, List<String> notParams, Boolean firstParent)
			throws IOException {
		if (timespan == null) {
			timespan = true;
		}
		List<Field> fields = new ArrayList<>();
		Map<String, Field> map = new HashMap<>();
		while (pp != null) {// 当父类为null的时候说明到达了最上层的父类(Object类).
			if(!firstParent)
				fields.addAll(Arrays.asList(pp.getDeclaredFields()));
			else{
				fields.addAll(0,Arrays.asList(pp.getDeclaredFields()));
			}
			pp = pp.getSuperclass(); // 得到父类,然后赋给自己
		}
		String[] headers;
		if (CollectionUtils.isEmpty(params)) {
			params = new ArrayList<String>();
			for (Field field : fields) {
				boolean fieldHasAnno = field.isAnnotationPresent(FieldTypeAnnotation.class);
				String key = StrUtil.HumpToUnderline(field.getName());
				if (fieldHasAnno && (notParams == null || !notParams.contains(key))) {
					params.add(key);
					map.put(key, field);
				}
			}
			headers = params.toArray(new String[params.size()]);
		} else {
			if (notParams != null) {
				params.removeAll(notParams);
			}
			headers = new String[params.size()];
			int i = 0;
			for (String key : params) {
				headers[i++] = StrUtil.HumpToUnderline(key);
			}
			for (Field field : fields) {
				boolean fieldHasAnno = field.isAnnotationPresent(FieldTypeAnnotation.class);
				String key = StrUtil.HumpToUnderline(field.getName());
				if (fieldHasAnno && (notParams == null || !notParams.contains(key))) {
					map.put(key, field);
				}
			}
		}
		List<Object[]> datas = new ArrayList<>(list.size());
		for (Map<Object, Object> m : list) {
			Object[] objects = new Object[headers.length];
			for (int j = 0; j < headers.length; j++) {
				Object o = m.get(headers[j]);
				if (o == null) {
					o = m.get(StrUtil.str2hump(headers[j]));
				}
				if (o != null && o instanceof Date) {
					o = CommUtil.formatLongDate(o);
				}
				objects[j] = o;
			}
			datas.add(objects);
		}
		String[] headers_tit = new String[params.size()];
		int j = 0;
		for (String key : params) {
			Field field = map.get(key);
			boolean fieldHasAnno = field.isAnnotationPresent(FieldTypeAnnotation.class);
			String exp;
			if (fieldHasAnno) {
				FieldTypeAnnotation fieldAnno = field.getAnnotation(FieldTypeAnnotation.class);
				// 输出注解属性
				exp = fieldAnno.exp();
			} else {
				exp = "暂无说明";
			}
			headers_tit[j++] = exp;
		}
		ExcelUtil.exportExcel(workbook, index,
				sheetName + (timespan ? CommUtil.formatTime("yyyy-MM-dd HH-mm-ss", new Date()) : ""), headers_tit,
				datas, outputStream);
	}
	
	public static void responseExecl(Workbook workbook, HttpServletResponse response, OutputStream outputStream) {
		ByteArrayOutputStream byteArrayOutputStream = null;
		try {
			byteArrayOutputStream = new ByteArrayOutputStream();
			workbook.write(byteArrayOutputStream);
			String suffix = ".xls";
			response.setContentType("application/vnd.ms-excel;charset=utf-8");
			response.setHeader("Content-Disposition", "attachment;filename="
					+ new String(("data-" + System.currentTimeMillis() + suffix).getBytes(), "iso-8859-1"));
			outputStream.write(byteArrayOutputStream.toByteArray());
			outputStream.close();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (byteArrayOutputStream != null) {
					byteArrayOutputStream.close();
				}
			} catch (IOException e) {
				e.printStackTrace();
			}
			try {
				workbook.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}
}
